package com.fc.tms.service.impl;

import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.annotation.Resource;
import javax.transaction.Transactional;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.fc.framework.BusinessException;
import com.fc.tms.constant.Course;
import com.fc.tms.constant.Team;
import com.fc.tms.constant.UserType;
import com.fc.tms.dao.JobTransferLogDAO;
import com.fc.tms.dao.TrainingLogDAO;
import com.fc.tms.entity.JobTransferLog;
import com.fc.tms.entity.TrainingLog;
import com.fc.tms.service.ExcelImportService;
import com.fc.tms.service.UserService;

@Service("excelImportService")
public class ExcelImportServiceImpl implements ExcelImportService {

    private static final Logger logger = LoggerFactory.getLogger(ExcelImportServiceImpl.class);

    private static final DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    private static Date DEFAULT_DATE = null;

    static {
        Calendar defaultC = Calendar.getInstance();
        defaultC.set(Calendar.YEAR, 2010);
        DEFAULT_DATE = defaultC.getTime();
    }

    @Resource
    UserService userService;

    @Resource
    JobTransferLogDAO jobTransferLogDAO;

    @Resource
    TrainingLogDAO trainingLogDAO;

    @Override
    @Transactional
    public Map<Integer, String> importTransferLog(String path) {
        Workbook wb = null;
        OPCPackage opcPackage = null;
        try {
            opcPackage = OPCPackage.open(path);
            wb = new XSSFWorkbook(opcPackage);
        } catch (Exception e) {
            logger.error("Open excel file error!", e);
            throw new BusinessException("上传文件错误!");
        }

        Map<Integer, String> msg = new HashMap<Integer, String>();
        try {

            Sheet sheet = wb.getSheetAt(0);
            if (sheet == null || sheet.getLastRowNum() < 1) {
                throw new BusinessException("第一个sheet为空!");
            }

            //首行
            if (sheet.getRow(0).getCell(0, Row.RETURN_BLANK_AS_NULL) == null) {
                throw new BusinessException("第一行数据为空!");
            }

            Cell firstCell = sheet.getRow(0).getCell(0, Row.RETURN_BLANK_AS_NULL);
            firstCell.setCellType(Cell.CELL_TYPE_STRING);

            if (!"M4".equals(firstCell.getStringCellValue().trim())) {
                throw new BusinessException("第一行第一个单元格内容不是M4，请确认EXCEL是否为转岗信息");
            }

            int index = 1;
            boolean isReadOver = false;
            do {
                try {
                    isReadOver = readTransferLog(sheet.getRow(index++));
                } catch (Exception e) {
                    logger.error(e.getMessage(), e);
                    msg.put(index, e.getMessage());
                }
            } while (!isReadOver);
        } catch (Exception e) {
            logger.error("Read file error", e);

            if (e instanceof BusinessException) {
                throw (BusinessException) e;
            } else {
                throw new BusinessException("系统错误：" + e.getMessage());
            }
        } finally {
            if (opcPackage != null) {
                try {
                    opcPackage.close();
                } catch (IOException e) {
                    logger.error("Close file error", e);
                }
            }
        }

        return msg;
    }

    @Transactional
    private void clearTransferLog() {
        jobTransferLogDAO.clear();
    }

    @Transactional
    private boolean readTransferLog(Row row) throws ParseException {
        if (row == null) {
            return true;
        }

        int i = 0;
        if (updateTransferLog(Team.M4, row, 0, 1))
            i++;
        if (updateTransferLog(Team.M3, row, 3, 4))
            i++;
        if (updateTransferLog(Team.ST, row, 6, 7))
            i++;
        if (updateTransferLog(Team.IB, row, 9, 10))
            i++;
        if (updateTransferLog(Team.AUTO_2, row, 12, 13))
            i++;
        if (updateTransferLog(Team.AUTO_1, row, 15, 16))
            i++;

        return i == 0;
    }

    @Transactional
    private boolean updateTransferLog(Team team, Row row, int nameIndex, int dateIndex) throws ParseException {
        Cell nameCell = row.getCell(nameIndex, Row.RETURN_BLANK_AS_NULL);
        Cell dateCell = row.getCell(dateIndex, Row.RETURN_BLANK_AS_NULL);

        if (nameCell == null) {
            return false;
        }

        Long studentId = userService.insertNotExists(nameCell.getStringCellValue(), UserType.STUDENT);

        JobTransferLog dbLog = jobTransferLogDAO.get(studentId, team, dateCell.getDateCellValue());
        if (dbLog != null && !df.format(dbLog.getTransferDate()).equals(df.format(dateCell.getDateCellValue()))) {
            dbLog.setTransferDate(dateCell.getDateCellValue());
            jobTransferLogDAO.update(dbLog);
        } else if (dbLog == null) {
            JobTransferLog log = new JobTransferLog();
            log.setStudentId(studentId);
            log.setTeam(team);
            log.setTransferDate(dateCell.getDateCellValue());
            jobTransferLogDAO.save(log);
        }

        return true;
    }

    @Override
    @Transactional
    public Map<Integer, String> importTrainingLog(String path) {
        Workbook wb = null;
        OPCPackage opcPackage = null;
        try {
            opcPackage = OPCPackage.open(path);
            wb = new XSSFWorkbook(opcPackage);
        } catch (Exception e) {
            logger.error("Open excel file error!", e);
            throw new BusinessException("上传文件错误!");
        }

        Map<Integer, String> msg = new HashMap<Integer, String>();
        try {

            Sheet sheet = wb.getSheetAt(0);
            if (sheet == null || sheet.getLastRowNum() < 1) {
                throw new BusinessException("第一个sheet为空!");
            }

            //首行
            if (sheet.getRow(0).getCell(0, Row.RETURN_BLANK_AS_NULL) == null) {
                throw new BusinessException("第一行数据为空!");
            }

            Cell firstCell = sheet.getRow(0).getCell(0, Row.RETURN_BLANK_AS_NULL);
            firstCell.setCellType(Cell.CELL_TYPE_STRING);

            if (!"金融".equals(firstCell.getStringCellValue().trim())) {
                throw new BusinessException("第一行第一个单元格内容不是金融，请确认EXCEL是否为培训信息");
            }

            clearTrainingLog();

            int index = 1;
            boolean isReadOver = false;
            do {
                try {
                    isReadOver = readTrainingLog(sheet.getRow(index++));
                } catch (Exception e) {
                    logger.error(e.getMessage(), e);
                    msg.put(index, e.getMessage());
                }
            } while (!isReadOver);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            if (e instanceof BusinessException) {
                throw (BusinessException) e;
            } else {
                throw new BusinessException("系统错误：" + e.getMessage());
            }
        } finally {
            if (opcPackage != null) {
                try {
                    opcPackage.close();
                } catch (IOException e) {
                    logger.error("Close file error", e);
                }
            }
        }

        return msg;
    }

    @Transactional
    private void clearTrainingLog() {
        this.trainingLogDAO.clear();
    }

    /**
     * 
     * @param row
     * @return isReadOver
     */
    @Transactional
    private boolean readTrainingLog(Row row) {
        if (row == null)
            return true;

        int i = 0;
        if (updateTrainingLog(Course.FINANCIAL_1, row, 0, 1, 2))
            i++;
        if (updateTrainingLog(Course.BASIC_COMMUNICATION, row, 4, 5, 6))
            i++;
        if (updateTrainingLog(Course.STRESS_MANAGEMENT, row, 8, 9, 10))
            i++;
        if (updateTrainingLog(Course.LISTENING_SKILL, row, 12, 13, 14))
            i++;
        if (updateTrainingLog(Course.EMPATHY, row, 16, 17, 18))
            i++;
        if (updateTrainingLog(Course.PROBLEM_SOLVING, row, 20, 21, 22))
            i++;
        if (updateTrainingLog(Course.CAREER_PLANNING, row, 24, 25, 26))
            i++;
        if (updateTrainingLog(Course.EMOTION_MANAGEMENT_2, row, 28, 29, 30))
            i++;
        if (updateTrainingLog(Course.LEGAL, row, 32, 33, 34))
            i++;

        return i == 0;
    }

    @Transactional
    private boolean updateTrainingLog(Course course, Row row, int studentIndex, int dateIndex, int trainerIndex) {
        Cell nameCell = row.getCell(studentIndex, Row.RETURN_BLANK_AS_NULL);
        Cell trainerCell = row.getCell(trainerIndex, Row.RETURN_BLANK_AS_NULL);
        Cell dateCell = row.getCell(dateIndex, Row.RETURN_BLANK_AS_NULL);

        if (nameCell == null || dateCell == null) {
            return false;
        }

        userService.insertNotExists(trainerCell.getStringCellValue(), UserType.TRAINTER);

        Long studentId = userService.insertNotExists(nameCell.getStringCellValue(), UserType.STUDENT);

        TrainingLog trainingLog = new TrainingLog();
        trainingLog.setStudentId(studentId);
        trainingLog.setCourse(course);

        try {
            trainingLog.setTrainingDate(dateCell.getDateCellValue());
        } catch (Exception e) {
            dateCell.setCellType(Cell.CELL_TYPE_STRING);
            String date = dateCell.getStringCellValue();
            if ("已培".equals(date)) {
                trainingLog.setTrainingDate(DEFAULT_DATE);
            }
        }

        trainingLogDAO.save(trainingLog);
        return true;
    }
}